/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package daos;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import models.*;
import util.WebConst;

/**
 *
 * @author trietto
 */
public class MemberDAO {

    private PreparedStatement pStmtSelectCount = null;
    private PreparedStatement pStmtSelectAll = null;
    private PreparedStatement pStmtSelectAllPerPage = null;
    private PreparedStatement pStmtLogin = null;
    private PreparedStatement pStmtSelect = null;
    private PreparedStatement pStmtInsert = null;
    private PreparedStatement pStmtDelete = null;
    private PreparedStatement pStmtUpdate = null;
    private static MemberDAO memberDAO = null;

    public static MemberDAO singleton() {
        if (memberDAO == null) {
            memberDAO = new MemberDAO();
        }
        return memberDAO;
    }

    public MemberDAO() {
        try {
            pStmtSelectCount = DBConnection.getConnection().prepareStatement("SELECT Count(MemberId) FROM Members");
            pStmtSelectAll = DBConnection.getConnection().prepareStatement("SELECT * FROM Members");
            pStmtLogin = DBConnection.getConnection().prepareStatement("SELECT * FROM Members WHERE MemberLogin=? AND MemberPassword=?");
            pStmtSelectAllPerPage = DBConnection.getConnection().prepareStatement("SELECT * FROM Members LIMIT ?,?");
            pStmtSelect = DBConnection.getConnection().prepareStatement("SELECT * FROM Members WHERE MemberId = ?");
            pStmtInsert = DBConnection.getConnection().prepareStatement("INSERT INTO Members("
                    + "MemberLevel,MemberLogin,MemberPassword,CardNumber,CardId,"
                    + "FirstName,LastName,Phone,Address,Email,Notes)"
                    + " VALUES(?,?,?,?,?,?,?,?,?,?,?)");
            pStmtDelete = DBConnection.getConnection().prepareStatement("DELETE FROM Members WHERE MemberId = ?");
            pStmtUpdate = DBConnection.getConnection().prepareStatement("UPDATE Members "
                    + "SET MemberLevel=?,MemberLogin=?,MemberPassword=?,CardNumber=?,CardId=?,"
                    + "FirstName=?,LastName=?,Phone=?,Address=?,Email=?,Notes=? WHERE MemberId = ?");
        } catch (SQLException ex) {
            Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public int recordCount() {
        int totalRecord = 0;
        try {
            ResultSet rs = pStmtSelectCount.executeQuery();
            while (rs.next()) {
                totalRecord = rs.getInt(1);
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(CardTypeDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return totalRecord;
    }

    public ArrayList<Member> selectAll() {
        ArrayList<Member> lstMembers = new ArrayList<Member>();
        try {
            ResultSet rs = pStmtSelectAll.executeQuery();
            while (rs.next()) {
                lstMembers.add(new Member(rs.getInt(1), rs.getInt(2),
                        rs.getString(3), rs.getString(4), rs.getString(5),
                        rs.getInt(6), rs.getString(7), rs.getString(8),
                        rs.getString(9), rs.getString(10),
                        rs.getString(11), rs.getString(12)));
            }
        } catch (SQLException ex) {
            Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstMembers;
    }

    public ArrayList<Member> selectAll(int page) {
        ArrayList<Member> lstMembers = new ArrayList<Member>();
        int offset = (page - 1) * WebConst.ROW_PER_PAGE;
        try {
            pStmtSelectAllPerPage.setInt(1, offset);
            pStmtSelectAllPerPage.setInt(2, WebConst.ROW_PER_PAGE);
            ResultSet rs = pStmtSelectAllPerPage.executeQuery();
            while (rs.next()) {
                lstMembers.add(new Member(rs.getInt(1), rs.getInt(2),
                        rs.getString(3), rs.getString(4), rs.getString(5),
                        rs.getInt(6), rs.getString(7), rs.getString(8),
                        rs.getString(9), rs.getString(10),
                        rs.getString(11), rs.getString(12)));
            }
        } catch (SQLException ex) {
            Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstMembers;
    }

    public Member select(int memberId) {
        Member member = null;
        try {
            pStmtSelect.setInt(1, memberId);
            ResultSet rs = pStmtSelect.executeQuery();
            while (rs.next()) {
                member = new Member(rs.getInt(1), rs.getInt(2),
                        rs.getString(3), rs.getString(4), rs.getString(5),
                        rs.getInt(6), rs.getString(7), rs.getString(8),
                        rs.getString(9), rs.getString(10),
                        rs.getString(11), rs.getString(12));
            }
        } catch (SQLException ex) {
            Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return member;
    }

        public Member login(String username, String password) {
        Member member = null;
        try {
            pStmtLogin.setString(1, username);
            pStmtLogin.setString(2, password);
            ResultSet rs = pStmtLogin.executeQuery();
            while (rs.next()) {
                member = new Member(rs.getInt(1), rs.getInt(2),
                        rs.getString(3), rs.getString(4), rs.getString(5),
                        rs.getInt(6), rs.getString(7), rs.getString(8),
                        rs.getString(9), rs.getString(10),
                        rs.getString(11), rs.getString(12));
            }
        } catch (SQLException ex) {
            Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return member;
    }

    public String insert(Member member) {
        String result = "";
        boolean bRet = false;
        int i = 1;
        try {
            pStmtInsert.setInt(i++, member.getMemberLevel());
            pStmtInsert.setString(i++, member.getMemberLogin());
            pStmtInsert.setString(i++, member.getMemberPassword());
            pStmtInsert.setString(i++, member.getCardNumber());
            pStmtInsert.setInt(i++, member.getCardId());
            pStmtInsert.setString(i++, member.getFirstName());
            pStmtInsert.setString(i++, member.getLastName());
            pStmtInsert.setString(i++, member.getPhone());
            pStmtInsert.setString(i++, member.getAddress());
            pStmtInsert.setString(i++, member.getEmail());
            pStmtInsert.setString(i++, member.getNotes());

            pStmtInsert.execute();
            bRet = true;
        } catch (SQLException ex) {
            Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        result = bRet ? "Successed" : "Failed";
        return result;
    }

    public String delete(int memberId) {
        String result = "";
        int numOfRows = 0;
        try {
            pStmtDelete.setInt(1, memberId);
            numOfRows = pStmtDelete.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        result = numOfRows > 0 ? "Successed" : "Failed";

        return result;
    }

    public String update(Member member) {
        String result = "";
        int numOfRows = 0;
        int i = 1;
        try {
            pStmtUpdate.setInt(i++, member.getMemberLevel());
            pStmtUpdate.setString(i++, member.getMemberLogin());
            pStmtUpdate.setString(i++, member.getMemberPassword());
            pStmtUpdate.setString(i++, member.getCardNumber());
            pStmtUpdate.setInt(i++, member.getCardId());
            pStmtUpdate.setString(i++, member.getFirstName());
            pStmtUpdate.setString(i++, member.getLastName());
            pStmtUpdate.setString(i++, member.getPhone());
            pStmtUpdate.setString(i++, member.getAddress());
            pStmtUpdate.setString(i++, member.getEmail());
            pStmtUpdate.setString(i++, member.getNotes());
            numOfRows = pStmtUpdate.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        result = numOfRows > 0 ? "Successed" : "Failed";

        return result;
    }
}
